package com.edu.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.edu.dao.BaseDao;
import com.edu.dao.UserBackgroundDao;
import com.edu.entity.User;
import com.edu.entity.shopDingdong;

public class UserBackgroundDaoImpl extends BaseDao implements UserBackgroundDao{

	@Override
	public List<User> queryUser(int currentPageNum, int pageSize) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();//打开连接
		String sql = "SELECT * FROM users LIMIT "+currentPageNum+","+pageSize+"";
		System.out.println(sql);
		List<User> list = new ArrayList<User>();
		PreparedStatement ps= null;
		ResultSet rs=null;
		try {
			ps= conn.prepareStatement(sql);
			rs = ps.executeQuery();			
			while(rs.next()) {
				User user = new User();
				user.setUserId(rs.getLong("userId"));
				user.setUserName(rs.getString("userName"));
				user.setUserPass(rs.getString("userPass"));
				user.setPhone(rs.getLong("phone"));
				user.setPosition(rs.getString("position"));
				user.setState(rs.getInt("state"));
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return list;
	}

	@Override
	public int count(String tableName) {
		// TODO Auto-generated method stub
		String sql="SELECT COUNT(1) as num FROM "+tableName+"";
		System.out.println(sql);
		ResultSet rs = super.queryResultSet(sql);
		try {
			while(rs.next()) {
				return rs.getInt("num");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return -1;
	}

	@Override
	public int deleteById(int userId,String tableId,String tableName) {
		// TODO Auto-generated method stub
		String sql="delete FROM "+tableName+" WHERE "+tableId+"="+userId;
		System.out.println(sql);
		int rowNum = super.executeUpdate(sql);
		return rowNum;
	}

	@Override
	public List<shopDingdong> queryShop(int currentPageNum,int pageSize) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();//打开连接
		String sql = "SELECT * FROM shopDingdong LIMIT "+currentPageNum+","+pageSize+"";
		System.out.println(sql);
		List<shopDingdong> list = new ArrayList<shopDingdong>();
		PreparedStatement ps= null;
		ResultSet rs=null;
		try {
			ps= conn.prepareStatement(sql);
			rs = ps.executeQuery();			
			while(rs.next()) {
				shopDingdong  shopDing=new shopDingdong();
				shopDing.setsId(rs.getLong("sId"));
				shopDing.setsName(rs.getString("sName"));
				shopDing.setsUrl(rs.getString("sUrl"));
				shopDing.setsPrice(rs.getDouble("sPrice"));
				shopDing.setsType(rs.getString("sType"));
				list.add(shopDing);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return list;
	}
	
	@Override
	public List<User> selectByUser(User u,int currentPageNum,int pageSize) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();//打开连接
		PreparedStatement ps= null;
		ResultSet rs=null;

		String sql = "SELECT * FROM users WHERE 1=1 ";
		if(u.getUserId() != -1) {
			sql += "AND userId like '%"+u.getUserId()+"%'";
		}
		if(!"".equals(u.getUserName()) && u.getUserName()!=null) {
			sql += "AND userName like '%"+u.getUserName()+"%'";
		}
		if(!"".equals(u.getUserPass()) && u.getUserPass()!=null) {
			sql += "AND userPass like '%"+u.getUserPass()+"%'";
		}
		
		if(!"".equals(u.getPosition()) && u.getPosition()!=null) {
			sql += "AND position like '%"+u.getPosition()+"%'";
		}
		if(u.getPhone() != -1) {
			sql += "AND phone like '%"+u.getPhone()+"%'";
		}
		if(u.getState() != -1) {
			sql += "AND state like '%"+u.getState()+"%'";
		}	
		sql+="LIMIT "+currentPageNum+","+pageSize+"";
		System.out.println(sql);
		
		
	
		List<User> list = new ArrayList<User>();
		try {
			ps= conn.prepareStatement(sql);
			rs = ps.executeQuery();	
			while(rs.next()) {
				User user = new User();
				user.setUserId(rs.getLong("userId"));
				user.setUserName(rs.getString("userName"));
				user.setUserPass(rs.getString("userPass"));
				user.setPhone(rs.getLong("phone"));
				user.setPosition(rs.getString("position"));
				user.setState(rs.getInt("state"));
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return list;
	}
	
	@Override
	public List<shopDingdong> selectByShop(shopDingdong shop,int currentPageNum,int pageSize) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();//打开连接
		PreparedStatement ps= null;
		ResultSet rs=null;

		String sql = "SELECT * FROM shopdingdong WHERE 1=1 ";
		if(shop.getsId() != -1) {
			sql += "AND sId like '%"+shop.getsId()+"%'";
		}
		if(!"".equals(shop.getsUrl()) && shop.getsUrl()!=null) {
			sql += "AND sUrl like '%"+shop.getsUrl()+"%'";
		}
		if(!"".equals(shop.getsName()) && shop.getsName()!=null) {
			sql += "AND sName like '%"+shop.getsName()+"%'";
		}
		if(shop.getsPrice()!=-1) {
			sql += "AND sPrice like '%"+shop.getsPrice()+"%'";
		}
		if(!"".equals(shop.getsType()) && shop.getsType()!=null) {
			sql += "AND sType like '%"+shop.getsType()+"%'";
		}

		sql+="LIMIT "+currentPageNum+","+pageSize+"";
		System.out.println(sql);
		
		
	
		List<shopDingdong> list = new ArrayList<shopDingdong>();
		try {
			ps= conn.prepareStatement(sql);
			rs = ps.executeQuery();	
			while(rs.next()) {
				shopDingdong shopDing = new shopDingdong();
				shopDing.setsId(rs.getLong("sId"));
				shopDing.setsName(rs.getString("sName"));
				shopDing.setsUrl(rs.getString("sUrl"));
				shopDing.setsPrice(rs.getDouble("sPrice"));
				shopDing.setsType(rs.getString("sType"));
				list.add(shopDing);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return list;
	}

	@Override
	public int addUser(User user) {
		// TODO Auto-generated method stub
		String sql="INSERT INTO users(userName,userPass,phone,position,state)VALUES (?,?,?,?,?)";
		System.out.println(sql);
		Object[] param = {user.getUserName(),user.getUserPass(),user.getPhone(),user.getPosition(),user.getState()};
		int rowNum = super.executeUpdate(sql,param);
		return rowNum;
	}

	@Override
	public int addShop(shopDingdong shop) {
		// TODO Auto-generated method stub
		String sql="INSERT INTO shopdingdong(sUrl,sName,sPrice,sType)VALUES (?,?,?,?)";
		System.out.println(sql);
		Object[] param = {shop.getsUrl(),shop.getsName(),shop.getsPrice(),shop.getsType()};
		int rowNum = super.executeUpdate(sql,param);
		return rowNum;
	}

	@Override
	public int updateUserById(User user) {
		// TODO Auto-generated method stub
		String sql="UPDATE users SET userName=?,userPass=?,phone=?,position=?,state=? WHERE userId=?";
		System.out.println(sql);
		
		Object[] param = {user.getUserName(),user.getUserPass(),user.getPhone(),user.getPosition(),user.getState(),user.getUserId()};
		int rowNum = super.executeUpdate(sql,param);
		return rowNum;
	}

	@Override
	public int updateShopById(shopDingdong shop) {
		// TODO Auto-generated method stub
		String sql="UPDATE shopdingdong SET sUrl=?,sName=?,sPrice=?,sType=? WHERE sId=?";
		System.out.println(sql);
		
		Object[] param = {shop.getsUrl(),shop.getsName(),shop.getsPrice(),shop.getsType(),shop.getsId()};
		int rowNum = super.executeUpdate(sql,param);
		return rowNum;
	}
	
}
